﻿using System;
using System.Collections.Generic;
using System.Data.Entity.Infrastructure;
using System.Data.Objects;
using System.Linq;
using System.Web;
using System.Web.Mvc;

namespace LabEntityIntro.Controllers
{
    public class LinQryController : Controller
    {
        //
        // GET: /LinQry/

        public ActionResult Index()
        {
            return View();
        }

        public ActionResult QryCat()
        {
            var db = new Models.CATALOG_DBEntities();
            var qry = from it in db.Categories
                      where it.Name.StartsWith("E")
                      select it;
            var data = qry.ToList();
            return View(data);
        }

        public ActionResult QryProd()
        {
            var db = new Models.CATALOG_DBEntities();
            using (db)
            {
                var qry = from it in db.Products.Include("Category")
                          select it;
                var data = qry.ToList();
                return View(data);
            }
        }

        public ActionResult QryGrp()
        {
            var db = new Models.CATALOG_DBEntities();
            using (db)
            {
                var linq = from it in db.Products
                           select it;
                var sql = ((DbQuery<Models.Product>)linq).ToString();

                ViewBag.Sql = sql;
                ViewData["linqCount"] = linq.Count();

                var products = db.Products.ToList();
                var qry = from it in products
                          group it by it.Name.First() into pgroup
                          select new Models.ProductGroup
                          {
                              FirstLetter = pgroup.Key.ToString(),
                              Count = pgroup.Count(),
                          };
                var data = qry.ToList();
                return View(data);
            }
        }

        public ActionResult QryBoth()
        {
            var db = new Models.CATALOG_DBEntities();

            var qry = from it in db.Categories
                      select it;
            var dataCat = qry.ToList();

            var qryPro = from it in db.Products.Include("Category")
                      select it;
            var dataPro = qryPro.ToList();

            var data = new Models.BothData
            {
                Categories = dataCat,
                Products = dataPro,
            };

            return View(data);
        }

    }
}
